#!/bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive
SQOOP_HOME=/usr/bin/

$HIVE_CMD -S -f "/root/Liam/online_edu/交付文件/create_test.sql"


${HIVE_HOME} -S -e "

drop table if exists signup_dwb.customer_singup_detail;
CREATE TABLE if not exists signup_dwb.customer_singup_de
(--ft_customer_relationship:11个字段
dt                           string,
id                          int,
create_date_time            string,
update_date_time            string comment '最后更新时间,可做拉链表',
deleted                     int comment '是否被删除（禁用）',
customer_id                 int comment '所属客户id',
first_id                    int comment '第一条客户关系id',
origin_type                 string comment '数据来源',
creator_name                string comment '创建者姓名',
origin_channel              string comment '来源渠道',
payment_time                string comment '支付状态变动时间',
payment_state               string comment '支付状态',

--dim_itcast_clazz:4个字段
itcast_school_id    string comment 'ems校区ID',
itcast_school_name  string comment 'ems校区名称',
itcast_subject_id   string comment 'ems学科ID',
itcast_subject_name string comment 'ems学科名称',

--ft_customer_clue:1个字段
customer_relationship_id int comment '客户关系id',


--ft_customer_appeal:6个字段
customer_relationship_first_id int comment '第一条客户关系id',
employee_id                    int comment '申诉人',
employee_name                  string comment '申诉人姓名',
employee_department_id         int comment '申诉人部门',
employee_tdepart_id            int comment '申诉人所属部门',
appeal_status                  string comment '申诉状态，Effective:无效 Invalid：有效',



--dim_employee：5个字段
creator             string comment '创建人',
real_name           string comment '员工的真实姓名',
department_id       string comment 'OA中的部门编号，有负值',
department_name     string comment 'OA中的部门名',
tdepart_id          int comment '直属部门',

--dim_scrm_department：2个字段
name             string comment '部门名称',
parent_id        int comment '父部门id'
)
COMMENT '客户报名宽表'

row format delimited fields terminated by '\t'
stored as TextFile;


-------------------------------------------------------------------------------------------------------------------------------------
--开启非严格模式
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--关闭mapjoin，增大map和reduce内存
set hive.auto.convert.join=false;


"
${PRESTO_HOME} --catalog hive --server hadoop01:8090 --execute "
-- 插入数据：
insert into signup_dwb.customer_singup_de
select
--ft_customer_relationship:11个字段
       substring(re.create_date_time,1,10) as dt,
       re.id,
       re.create_date_time,
       re.update_date_time,
       re.deleted,
       re.customer_id,
       re.first_id,
       case  when re.origin_type = 'NETSERVICE' or re.origin_type= 'PRESIGNUP' then 'online'
             else 'offline'
             end as origin_type,
       re.creator_name,
       cc.origin_channel,
       re.payment_time,
       re.payment_state,
--dim_itcast_clazz:4个字段
       ic.itcast_school_id ,
       ic.itcast_school_name ,
       ic.itcast_subject_id  ,
       ic.itcast_subject_name,
--ft_customer_clue:1个字段
       cc.customer_relationship_id,

--ft_customer_appeal:6个字段
       ca.customer_relationship_first_id,
       ca.employee_id,
       ca.employee_name,
       ca.employee_department_id,
       ca.employee_tdepart_id,
       case  when  ca.appeal_status = 1 then 'Effective'
           else 'Invalid'
           end as appeal_status,
--dim_employee：5个字段
       emp.creator,
       emp.real_name,
       emp.department_id,
       emp.department_name,
       emp.tdepart_id,
--dim_scrm_department：3个字段
       sd.name,
       sd.parent_id


from
ft_customer_relationship re
left join
dim_itcast_clazz ic on re.itcast_clazz_id = ic.id
left join
ft_customer_clue cc on re.id = cc.customer_relationship_id
left join
ft_customer_appeal ca on cc.customer_relationship_id =ca.customer_relationship_first_id
left join
dim_employee emp on re.id=emp.creator
left join
dim_scrm_department sd on emp.tdepart_id = sd.id;"


